package com.monitorRoutine.monitorRoutineServer.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.monitorRoutine.monitorRoutineServer.entity.SqlResultVo;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.util.*;

public class ExcelUtil {
    public static void addDataToExcel(List<List<String>> data, String sheetName, String filePath) {
        FileInputStream fis = null;
        FileOutputStream fos = null;
        Workbook workbook = null;
        Sheet sheet = null;

        try {
            File file = new File(filePath);
            if (!file.exists()) {
                workbook = new XSSFWorkbook();
            } else {
                // 打开现有的Excel文件
                fis = new FileInputStream(new File(filePath));
                workbook = WorkbookFactory.create(fis);
            }

            // 获取工作表，如果不存在则创建
            sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                sheet = workbook.createSheet(sheetName);
            }
            // 添加数据到工作表
            int rowIndex = sheet.getLastRowNum() + 1;
            for (List<String> rowData : data) {
                Row row = sheet.createRow(rowIndex++);
                for (int col = 0; col < rowData.size(); col++) {
                    Cell cell = row.createCell(col);
                    cell.setCellValue(rowData.get(col));
                }
                // 在每组数据后空两行
                rowIndex += 2;
            }
            // 写入修改后的Excel文件
            fos = new FileOutputStream(new File(filePath));
            workbook.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (fos != null) fos.close();
                if (fis != null) fis.close();
                if (workbook != null) workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static void addSqlResultToExcel(List<SqlResultVo> sqlResultVos, String sheetName, String filePath) {
        sheetName = sheetName.replaceAll(":", "_");
        HashMap<String, List<SqlResultVo>> stringListHashMap = new HashMap<>();
        for (SqlResultVo sqlResultVo : sqlResultVos) {
            List<SqlResultVo> sqlResultList = stringListHashMap.get(sqlResultVo.getSetname());
            if (sqlResultList == null) {
                sqlResultList = new ArrayList<SqlResultVo>();
                stringListHashMap.put(sqlResultVo.getSetname(), sqlResultList);
            }
            sqlResultList.add(sqlResultVo);
        }


        FileInputStream fis = null;
        FileOutputStream fos = null;
        Workbook workbook = null;
        Sheet sheet = null;

        try {
            File file = new File(filePath);
            if (!file.exists()) {
                workbook = new XSSFWorkbook();
            } else {
                // 打开现有的Excel文件
                fis = new FileInputStream(new File(filePath));
                workbook = WorkbookFactory.create(fis);
            }

            // 获取工作表，如果不存在则创建
            sheet = workbook.getSheet(sheetName);
            if (sheet == null) {


                sheet = workbook.createSheet(sheetName);
            }
            // 添加数据到工作表
            for (String s : stringListHashMap.keySet()) {
                writeSheet(sheet, workbook, stringListHashMap.get(s));
            }


            // 写入修改后的Excel文件
            fos = new FileOutputStream(new File(filePath));
            workbook.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (fos != null) fos.close();
                if (fis != null) fis.close();
                if (workbook != null) workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    static void writeSheet(Sheet sheet, Workbook workbook, List<SqlResultVo> sqlResultVos) {
        CellStyle cellStyle_set = workbook.createCellStyle();
        XSSFColor customColor_set = new XSSFColor(new byte[]{(byte) 169, (byte) 229, (byte) 169});
        cellStyle_set.setFillForegroundColor(customColor_set);
        CellStyle cellStyle_list = workbook.createCellStyle();
        XSSFColor customColor_list = new XSSFColor(new byte[]{(byte) 169, (byte) 229, (byte) 169});
        cellStyle_list.setFillForegroundColor(customColor_list);
        CellStyle cellStyle_field = workbook.createCellStyle();
        XSSFColor customColor_field = new XSSFColor(new byte[]{(byte) 169, (byte) 229, (byte) 169});
        cellStyle_field.setFillForegroundColor(customColor_field);
        CellStyle cellStyle_tble = workbook.createCellStyle();
        XSSFColor customColor_tble = new XSSFColor(new byte[]{(byte) 178, (byte) 177, (byte) 160});
        cellStyle_tble.setFillForegroundColor(customColor_tble);

        int rowIndex = sheet.getLastRowNum() + 1;
        if (rowIndex > 0) {
            rowIndex = rowIndex + 2;
        }
        /**
         * 写入集合名
         */

        Row row_setname = sheet.createRow(rowIndex);
        Cell cellSetName = row_setname.createCell(0);
        SqlResultVo sqlResultVo = sqlResultVos.get(0);
        cellSetName.setCellValue(sqlResultVo.getSetname());
        cellSetName.setCellStyle(cellStyle_set);


        /**
         * 写入集合内容
         */
        for (SqlResultVo rowData : sqlResultVos) {
            /**
             * 写入结果
             */
            //写入结果名和执行的sql


            rowIndex = rowIndex + 1;
            Row row_resultname = sheet.createRow(rowIndex);
            Cell cellName = row_resultname.createCell(1);
            cellName.setCellValue(rowData.getSqlRecord().getName());
            cellName.setCellStyle(cellStyle_list);

            Cell cellExecutesql = row_resultname.createCell(2);
            cellExecutesql.setCellValue(rowData.getSqlRecord().getExecutesql());
            cellExecutesql.setCellStyle(cellStyle_list);

            //写结果字段
            List<String> parse = JSON.parseArray(rowData.getSqlField(), String.class);
            rowIndex = rowIndex + 1;
            Row row = sheet.createRow(rowIndex);

            if (parse == null || parse.size() <= 0) {
                Cell cell = row.createCell(2);
                cell.setCellValue(rowData.getSqlResult());
                continue;
            }


            for (int col = 0; col < parse.size(); col++) {
                if (parse.get(col) == null) {
                    continue;
                }
                Cell cell = row.createCell(col + 2);
                cell.setCellValue(parse.get(col));
                cell.setCellStyle(cellStyle_field);
            }


            //写入结果
            List<List> lists = JSON.parseArray(rowData.getSqlResult(), List.class);
            for (List list : lists) {
                rowIndex = rowIndex + 1;
                Row row_zw = sheet.createRow(rowIndex);
                for (int col = 0; col < list.size(); col++) {
                    if (list.get(col) == null) {
                        continue;
                    }
                    Cell cell = row_zw.createCell(col + 2);

                    cell.setCellValue(list.get(col).toString());
                    cell.setCellStyle(cellStyle_tble);

                }

            }
            rowIndex = rowIndex + 1;
        }
    }

    public static void main(String[] args) {
        HashMap<String, String> stringStringHashMap = new HashMap<>();

        stringStringHashMap.put("entity_1_9","啊啊");
        stringStringHashMap.put("entity_2_9","啊啊");
        stringStringHashMap.put("entity_3_9","啊啊");
        stringStringHashMap.put("entity_4_9","啊啊");
        stringStringHashMap.put("entity_5_9","啊啊");
        stringStringHashMap.put("entity_6_9","啊啊");
        stringStringHashMap.put("entity_7_9","啊啊");
        stringStringHashMap.put("entity_8_9","啊啊");
        stringStringHashMap.put("entity_9_9","啊啊");

        templates("D:\\file\\dataAnalysisTemplates_1.xlsx",stringStringHashMap,"D:\\file\\test.xlsx");
    }



    static public void templates(String templatesPath, Map<String, String> mapData, String filePath) {
        /**
         * 复制模板
         * 填充模板
         */
        try {
            // 复制模板
            Path source = Paths.get(templatesPath);
            Path destination = Paths.get(filePath);
            // 使用REPLACE_EXISTING选项覆盖已存在的文件
            Files.copy(source, destination, StandardCopyOption.REPLACE_EXISTING);
            //读取复制后文件

            try (FileInputStream fis = new FileInputStream(filePath)) {
                // 使用WorkbookFactory创建XSSFWorkbook实例
                Workbook workbook = WorkbookFactory.create(fis);
                for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                    Sheet sheet = workbook.getSheetAt(i);
                    for (Row row : sheet) {
                        // 遍历行中的所有单元格
                        for (Cell cell : row) {
                            if (cell.getCellType() == CellType.STRING) {
                                Map<String, Object> validJson = JSONUtil.isValidJson(cell.getStringCellValue());
                                if (validJson == null) {
                                    continue;
                                }
                                String name = (String) validJson.get("name");
                                String type = (String) validJson.get("type");
                                String nameVlue = mapData.get(name);
                                cell.setCellValue(nameVlue);
                            }
                        }
                    }
                }
                // 获取工作表

                // 遍历工作表中的所有行


                // 保存更改
                try (FileOutputStream fos = new FileOutputStream(filePath)) {
                    workbook.write(fos);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

//    String analysisJSON(String s,String type){
//        if ("string".equals(type)) {
//            if (JSONUtil.isValidJson(s)==null) {
//                return s;
//            }
//            ArrayList<ArrayList<String>> table = null;
//            ObjectMapper mapper = new ObjectMapper();
//
//            try {
//                List<List> lists = JSON.parseArray(s, List.class);
//                String o =(String) lists.get(0).get(0);
//                return o;
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//        }else
//        if ("table".equals(type)) {
//            return s;
//        }else {
//            return "解析错误";
//        }
//
//    }

}
